Database Schema - DevolutionSync

Overview

DevolutionSync uses MySQL 8.0 as its relational database system. The schema is designed to handle product returns, deviations, user management, and audit trails with proper normalization and referential integrity.

Database Information

Database Name

devolutionsync

Character Set

utf8

Tables

5 tables

Entity Relationship Overview

┌─────────────────┐
│    usuarios     │
│  (USR: PK)      │
└────────┬────────┘
         │ 1:N
         │
         ▼
┌─────────────────────────────┐
│      devoluciones           │
│  • id (PK)                  │
│  • usuario_creador (FK)     │──┐
│  • usuario_revisor (FK)     │  │
│  • estado (ENUM)            │  │
│  • motivo (ENUM)            │  │
└──────────┬──────────────────┘  │ 1:N
           │ 1:N                 │
           │                     ▼
           │            ┌─────────────────┐
           └───────────>│ notificaciones  │
                        │ • id (PK)       │
                        │ • id_devolucion │
                        └─────────────────┘

┌─────────────────┐
│  login_attempts │
│  (Security)     │
└─────────────────┘

┌─────────────────┐
│    producto     │
│  (Catalog)      │
└─────────────────┘

Table Definitions

devoluciones

Core table storing all product return and deviation records.

Script_DB.sql

CREATE TABLE IF NOT EXISTS devoluciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nit VARCHAR(20) NOT NULL,
    nombre_cliente VARCHAR(255) NOT NULL,
    direccion TEXT,
    codigo_producto VARCHAR(50) NOT NULL,
    descripcion_producto TEXT,
    unidad VARCHAR(20),
    kg DECIMAL(10,2),
    motivo ENUM('devolucion', 'faltante', 'sobrante') NOT NULL,
    cantidad_und INT,
    cantidad_kg DECIMAL(10,2),
    evidencia VARCHAR(255),
    observacion TEXT,
    estado ENUM('pendiente', 'aprobado', 'rechazado') DEFAULT 'pendiente',
    observacion_admin TEXT,
    codigo_admin VARCHAR(50),
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_revision TIMESTAMP NULL,
    usuario_creador VARCHAR(50) NOT NULL,
    usuario_revisor VARCHAR(50)
);

Field Descriptions

Customer Information

Field	Type	Description
`nit`	VARCHAR(20)	Customer tax identification number
`nombre_cliente`	VARCHAR(255)	Customer full name
`direccion`	TEXT	Customer delivery address

Product Information

Field	Type	Description
`codigo_producto`	VARCHAR(50)	Product SKU/code
`descripcion_producto`	TEXT	Product description
`unidad`	VARCHAR(20)	Unit of measure (e.g., “UND”, “KG”)
`kg`	DECIMAL(10,2)	Product weight in kilograms

Deviation Details

Field	Type	Description
`motivo`	ENUM	Reason: `devolucion`, `faltante`, `sobrante`
`cantidad_und`	INT	Quantity in units
`cantidad_kg`	DECIMAL(10,2)	Quantity in kilograms
`evidencia`	VARCHAR(255)	File path to uploaded evidence
`observacion`	TEXT	Initial observations from creator

Review Information

Field	Type	Description
`estado`	ENUM	Status: `pendiente`, `aprobado`, `rechazado`
`observacion_admin`	TEXT	Administrator’s review comments
`codigo_admin`	VARCHAR(50)	Authorization code from admin
`usuario_revisor`	VARCHAR(50)	Username who reviewed (FK to usuarios)
`fecha_revision`	TIMESTAMP	Date/time of review

Audit Trail

Field	Type	Description
`id`	INT	Auto-increment primary key
`fecha_creacion`	TIMESTAMP	Automatic creation timestamp
`usuario_creador`	VARCHAR(50)	Username who created record (FK to usuarios)

Estado Enum Values

'pendiente' -- Default state when deviation is created
           -- Waiting for administrator review

Motivo Enum Values

devolucion
faltante
sobrante

Product Return: Customer returning product due to defects, quality issues, or order errors.Common scenarios:

Damaged goods
Wrong product delivered
Quality not meeting specifications

usuarios

User authentication and role management table.

Script_DB.sql

CREATE TABLE `usuarios` (
    `USR` VARCHAR(50) NOT NULL,
    `PAS` VARCHAR(50) NOT NULL,
    `NOMBRE` VARCHAR(100) NOT NULL,
    `GRADO` INT NOT NULL,
    PRIMARY KEY (`USR`)
)

Field Descriptions

Field	Type	Description
`USR`	VARCHAR(50)	Username (Primary Key)
`PAS`	VARCHAR(50)	Password (plaintext - should be hashed in production)
`NOMBRE`	VARCHAR(100)	Full name of user
`GRADO`	INT	Role level: 1=Admin, 2=Auxiliary, 3=Consultant

User Roles (GRADO)

Administrator

GRADO = 1

Review deviations
Approve/reject requests
View all records
Access dashboard

Auxiliary

GRADO = 2

Create deviations
Upload evidence
View own records
Limited access

Consultant

GRADO = 3

Read-only access
View history
Search records
No modifications

Sample Data

Script_DB.sql

INSERT INTO `usuarios` (`USR`, `PAS`, `NOMBRE`, `GRADO`) VALUES 
    ('ANALISTA', '1088350785', 'SEBASTIAN OBANDO', 1),
    ('AUXILIAR', '895623', 'AUXILIAR', 2),
    ('CONSULTA', '895623', 'CONSULTA', 3);

Passwords are stored in plaintext in the current implementation. Production systems should use password_hash() and password_verify() with bcrypt or Argon2.

notificaciones

Notification system for user alerts and status updates.

Script_DB.sql

CREATE TABLE IF NOT EXISTS notificaciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_devolucion INT NOT NULL,
    mensaje TEXT NOT NULL,
    leida BOOLEAN DEFAULT FALSE,
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    usuario_destino VARCHAR(50) NOT NULL,
    FOREIGN KEY (id_devolucion) REFERENCES devoluciones(id)
);

Field Descriptions

Field	Type	Description
`id`	INT	Auto-increment primary key
`id_devolucion`	INT	Foreign key to devoluciones table
`mensaje`	TEXT	Notification message content
`leida`	BOOLEAN	Read status (FALSE = unread)
`fecha`	TIMESTAMP	Notification creation timestamp
`usuario_destino`	VARCHAR(50)	Target user (FK to usuarios.USR)

Foreign Key Relationship

FOREIGN KEY (id_devolucion) REFERENCES devoluciones(id)

Notifications are automatically created when:

Administrator approves a deviation
Administrator rejects a deviation
Status changes require user attention

login_attempts

Security table tracking failed login attempts for brute-force protection.

Script_DB.sql

CREATE TABLE IF NOT EXISTS login_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    username VARCHAR(50) NOT NULL,
    attempts INT DEFAULT 0,
    last_attempt DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Field Descriptions

Field	Type	Description
`id`	INT	Auto-increment primary key
`ip_address`	VARCHAR(45)	Client IP address (supports IPv6)
`username`	VARCHAR(50)	Attempted username
`attempts`	INT	Number of failed attempts
`last_attempt`	DATETIME	Timestamp of most recent attempt
`created_at`	TIMESTAMP	First attempt timestamp

Security Features

Track Failed Logins

Record IP address and username for each failed login attempt

Increment Counter

Increase attempt counter for each failure

Enforce Lockout

Block login after threshold (e.g., 5 attempts)

Time-Based Reset

Clear attempts after cooldown period

producto

Product catalog with item codes, descriptions, and specifications.

Script_DB.sql

CREATE TABLE producto (
    id INT(11) NOT NULL PRIMARY KEY,
    item INT(11) NOT NULL,
    description VARCHAR(50) NOT NULL,
    minimo FLOAT NOT NULL,
    maximo FLOAT NOT NULL,
    pesoProm FLOAT NOT NULL
);

Field Descriptions

Field	Type	Description
`id`	INT(11)	Auto-increment primary key
`item`	INT(11)	Product item code/SKU
`descripcion`	VARCHAR(50)	Product description
`minimo`	FLOAT	Minimum acceptable weight
`maximo`	FLOAT	Maximum acceptable weight
`pesoProm`	FLOAT	Average/standard weight

Sample Data

Script_DB.sql

INSERT INTO `producto` (`id`, `Item`, `descripcion`, `minimo`, `maximo`, `pesoProm`) 
VALUES (1, 15440, 'ALAS MR CONG BLS', 4, 6, 5);

The minimo, maximo, and pesoProm fields are used for weight validation when creating deviations. They help identify if reported quantities fall within acceptable ranges.

Indexes and Constraints

Primary Keys

devoluciones.id - Auto-increment integer
usuarios.USR - Varchar username
notificaciones.id - Auto-increment integer
login_attempts.id - Auto-increment integer
producto.id - Integer

Foreign Keys

-- usuario_creador references usuarios.USR
-- usuario_revisor references usuarios.USR
-- (Implicit FK, not enforced in current schema)

Recommended Indexes

For production performance, add these indexes:

Performance Indexes

-- Speed up status filtering
CREATE INDEX idx_devoluciones_estado ON devoluciones(estado);

-- Speed up date range queries
CREATE INDEX idx_devoluciones_fecha ON devoluciones(fecha_creacion);

-- Speed up user queries
CREATE INDEX idx_devoluciones_creador ON devoluciones(usuario_creador);

-- Speed up notification queries
CREATE INDEX idx_notificaciones_usuario ON notificaciones(usuario_destino, leida);

-- Speed up login attempt lookups
CREATE INDEX idx_login_ip_user ON login_attempts(ip_address, username);

Data Types and Constraints

Decimal Precision

kg DECIMAL(10,2)           -- Max: 99,999,999.99 kg
cantidad_kg DECIMAL(10,2)  -- Max: 99,999,999.99 kg

DECIMAL(10,2) provides exact precision for financial and weight calculations, avoiding floating-point rounding errors.

Timestamp Behavior

fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP
fecha_revision TIMESTAMP NULL

fecha_creacion: Automatically set on INSERT
fecha_revision: NULL until admin reviews record

ENUM Constraints

estado
motivo

ENUM('pendiente', 'aprobado', 'rechazado')
DEFAULT 'pendiente'

Ensures only valid status values. Rejects invalid data at database level.

ENUM('devolucion', 'faltante', 'sobrante')
NOT NULL

Required field. Prevents records without a defined reason.

Query Examples

Common Queries

SELECT * FROM devoluciones 
WHERE estado = 'pendiente' 
ORDER BY fecha_creacion ASC;

Database Setup

Initial Setup

# Connect to MySQL
mysql -u root -p

# Create database and import schema
source Script_BD/Script_DB.sql

# Verify tables
USE devolutionsync;
SHOW TABLES;
DESCRIBE devoluciones;

Connection from PHP

$pdo = Conexion::Conectar();
// Returns configured PDO instance with:
// - Exception error mode
// - Associative fetch mode
// - Real prepared statements

Migration Considerations

Production Recommendations:

Add password hashing (bcrypt/Argon2)
Create explicit foreign keys on devoluciones
Add indexes for performance
Implement soft deletes (deleted_at column)
Add audit trail (updated_at, updated_by)
Use InnoDB engine for transactions
Configure CASCADE rules for foreign keys

Next Steps

MVC Structure

Learn how models interact with this schema

Architecture

Understand overall system design

API Reference

View model methods and database operations

Deployment

Deploy database with Docker

​Overview

​Database Information

Database Name

Character Set

Tables

​Entity Relationship Overview

​Table Definitions

​devoluciones

​Field Descriptions

​Estado Enum Values

​Motivo Enum Values

​usuarios

​Field Descriptions

​User Roles (GRADO)

Administrator

Auxiliary

Consultant

​Sample Data

​notificaciones

​Field Descriptions

​Foreign Key Relationship

​login_attempts

​Field Descriptions

​Security Features

​producto

​Field Descriptions

​Sample Data

​Indexes and Constraints

​Primary Keys

​Foreign Keys

​Recommended Indexes

​Data Types and Constraints

​Decimal Precision

​Timestamp Behavior

​ENUM Constraints

​Query Examples

​Common Queries

​Database Setup

​Initial Setup

​Connection from PHP

​Migration Considerations

​Next Steps

MVC Structure

Architecture

API Reference

Deployment

Overview

Database Information

Entity Relationship Overview

Table Definitions

devoluciones

Field Descriptions

Estado Enum Values

Motivo Enum Values

usuarios

Field Descriptions

User Roles (GRADO)

Sample Data

notificaciones

Field Descriptions

Foreign Key Relationship

login_attempts

Field Descriptions

Security Features

producto

Field Descriptions

Sample Data

Indexes and Constraints

Primary Keys

Foreign Keys

Recommended Indexes

Data Types and Constraints

Decimal Precision

Timestamp Behavior

ENUM Constraints

Query Examples

Common Queries

Database Setup

Initial Setup

Connection from PHP

Migration Considerations

Next Steps